#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project ：123.py 
@File ：测试数据准备构建_GUI版本.py
@IDE  ：PyCharm 
@Author ：李涵彬
@Date ：2025/1/6 上午10:27 
"""

import tkinter as tk
from tkinter import filedialog, messagebox
from tkinter import ttk
import numpy as np
import pyodbc
import pandas as pd
import os
import shutil
from datetime import datetime
from sqlalchemy import create_engine

# SQL Server 配置
SQLSERVER_SERVER = '127.0.0.1'
SQLSERVER_PORT = '1433'
SQLSERVER_DATABASE = 'ColdRollingFormal'
SQLSERVER_USER = 'sa'
SQLSERVER_PASSWORD = 'ColdRolling'
SQLSERVER_CHARSET = 'UTF-8'

# 连接数据库获取数据
def fetch_data_from_sqlserver(server, database, table_name, username, password):
    conn_str = f'mssql+pyodbc://{username}:{password}@{server},{SQLSERVER_PORT}/{database}?driver=ODBC+Driver+17+for+SQL+Server&trusted_connection=yes'
    engine = create_engine(conn_str)
    query = f"SELECT roll_number, implementation_rate, evaluate, create_dt FROM {table_name}"
    df = pd.read_sql(query, engine)
    return df

# 获取某个字段的数据并转化为列表
def get_column_data(df, column_name):
    return df[column_name].tolist()

# 遍历列表，匹配文件夹并补全路径
def get_matching_folders(root_dir, search_list):
    folder_matches = []

    for item in search_list:
        match_folders = []
        for root, dirs, files in os.walk(root_dir):
            for dir_name in dirs:
                if str(item) in dir_name:
                    match_folders.append(os.path.join(root, dir_name))

        folder_matches.append(match_folders)

    return folder_matches

# 将两个文件夹复制到目标文件夹中
def copy_folders_to_target(folders, target_dir):
    for folder_list in folders:
        for folder in folder_list:
            if folder:
                # 获取对应的全数字文件夹
                digit_folder = [element for element in folder.split("_") if element.isdigit() and len(element) == 14]
                if digit_folder:
                    digit_folder = digit_folder[0]
                    digit_folder_path = os.path.join(root_dir, digit_folder)
                else:
                    continue

                target_folder = os.path.join(target_dir, os.path.basename(folder))
                target_digit_folder_path = os.path.join(target_folder, digit_folder)
                if os.path.exists(target_folder):
                    shutil.rmtree(target_folder)  # 删除已存在的文件夹

                if os.path.exists(folder) and os.path.exists(digit_folder_path):
                    shutil.copytree(folder, target_folder)
                    shutil.copytree(digit_folder_path, target_digit_folder_path)

# 将全为数字的文件夹名称从字符串解析为年月日时分秒格式
def parse_folder_name_to_datetime(folder_name):
    if folder_name.isdigit():
        timestamp = datetime.strptime(folder_name, '%Y%m%d%H%M%S')
        return timestamp.strftime('%Y年%m月%d日')
    return folder_name

# 将存在非数字的文件夹重命名
def rename_folder_with_date_and_review(folder_path, review_field_value):
    folder_name = os.path.basename(folder_path)
    date_prefix = [element for element in folder_name.split('_') if element.isdigit() and len(element) == 14][0]
    date_str = parse_folder_name_to_datetime(date_prefix)

    new_folder_name = f"{date_str}_{folder_name}_{review_field_value}"

    folder_path = os.path.join(target_dir, folder_name)
    new_folder_path = os.path.join(target_dir, new_folder_name)

    if os.path.exists(folder_path):
        os.rename(folder_path, new_folder_path)

    return new_folder_path

# 主函数，整合步骤
def process_files_and_folders(server, database, table_name, username, password, root_dir, target_dir, roll_number_length, evaluate_empty, date_threshold):
    # 1. 从数据库获取数据
    df = fetch_data_from_sqlserver(server, database, table_name, username, password)

    # 筛选条件
    df = df[df['roll_number'].str.len() == roll_number_length]
    if evaluate_empty:
        df = df[df['evaluate'].isnull()]
    df = df[df['create_dt'] > date_threshold]

    # 2. 获取目标列的数据
    roll_number_data = get_column_data(df, 'roll_number')  # 获取需要匹配的文件夹字段
    implementation_rate_data = get_column_data(df, 'implementation_rate')  # 获取评价字段

    # 3. 获取匹配的文件夹
    folder_matches = get_matching_folders(root_dir, roll_number_data)

    # 4. 复制文件夹到目标目录
    copy_folders_to_target(folder_matches, target_dir)

    # 6. 重命名文件夹并添加评价字段
    for i, folder_list in enumerate(folder_matches):
        for folder in folder_list:
            if folder:
                new_folder_path = rename_folder_with_date_and_review(folder, implementation_rate_data[i])
                print(f"Folder {folder} renamed to {new_folder_path}")

# GUI部分
def select_root_directory():
    global root_dir
    root_dir = filedialog.askdirectory()
    root_dir_entry.delete(0, tk.END)
    root_dir_entry.insert(0, root_dir)

def select_target_directory():
    global target_dir
    target_dir = filedialog.askdirectory()
    target_dir_entry.delete(0, tk.END)
    target_dir_entry.insert(0, target_dir)

def run_process():
    # 获取用户输入的筛选条件
    roll_number_length = int(roll_number_length_var.get())
    evaluate_empty = evaluate_empty_var.get()
    date_threshold = date_threshold_var.get()

    # 调用主处理函数
    process_files_and_folders(SQLSERVER_SERVER, SQLSERVER_DATABASE, 'plan_rate_evaluate', SQLSERVER_USER,
                              SQLSERVER_PASSWORD, root_dir, target_dir, roll_number_length, evaluate_empty, date_threshold)
    messagebox.showinfo("提示", "处理完成！")

# 创建主窗口
root = tk.Tk()
root.title("数据处理配置")

# 创建输入框和按钮
root_dir_label = tk.Label(root, text="根目录:")
root_dir_label.grid(row=0, column=0, padx=5, pady=5)
root_dir_entry = tk.Entry(root, width=50)
root_dir_entry.grid(row=0, column=1, padx=5, pady=5)
root_dir_button = tk.Button(root, text="选择", command=select_root_directory)
root_dir_button.grid(row=0, column=2, padx=5, pady=5)

target_dir_label = tk.Label(root, text="目标目录:")
target_dir_label.grid(row=1, column=0, padx=5, pady=5)
target_dir_entry = tk.Entry(root, width=50)
target_dir_entry.grid(row=1, column=1, padx=5, pady=5)
target_dir_button = tk.Button(root, text="选择", command=select_target_directory)
target_dir_button.grid(row=1, column=2, padx=5, pady=5)

roll_number_length_label = tk.Label(root, text="roll_number长度:")
roll_number_length_label.grid(row=2, column=0, padx=5, pady=5)
roll_number_length_var = tk.StringVar(value="14")
roll_number_length_entry = tk.Entry(root, textvariable=roll_number_length_var)
roll_number_length_entry.grid(row=2, column=1, padx=5, pady=5)

evaluate_empty_label = tk.Label(root, text="evaluate为空:")
evaluate_empty_label.grid(row=3, column=0, padx=5, pady=5)
evaluate_empty_var = tk.BooleanVar(value=True)
evaluate_empty_check = tk.Checkbutton(root, variable=evaluate_empty_var)
evaluate_empty_check.grid(row=3, column=1, padx=5, pady=5)

date_threshold_label = tk.Label(root, text="日期阈值(YYYY-MM-DD):")
date_threshold_label.grid(row=4, column=0, padx=5, pady=5)
date_threshold_var = tk.StringVar(value="2024-12-25")
date_threshold_entry = tk.Entry(root, textvariable=date_threshold_var)
date_threshold_entry.grid(row=4, column=1, padx=5, pady=5)

run_button = tk.Button(root, text="运行处理", command=run_process)
run_button.grid(row=5, column=1, padx=5, pady=20)

root.mainloop()